﻿namespace CRM.DataAccess.DAOs
{
    using System.Collections.Generic;
    using System.Data.Entity;
    using CRM.DataAccess.Entities;
    using System.Linq;
    using System.Data.Objects;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Validation;
    using System.Diagnostics;
    using System;

    public class ContactDAO : DbContext
    {
        public ContactDAO()
            : base("name=Entities")
        {
        }
        public DbSet<CONTACT> CONTACTS { get; set; }
        public DbSet<CONTACT_DETAIL> CONTACT_DETAIL { get; set; }
        public DbSet<CONTACT_STATUS> CONTACT_STATUS { get; set; }
        public DbSet<SALUTATION> SALUTATIONS { get; set; }

        public DbSet<APPOINTMENT> APPOINTMENTS { get; set; }
        public DbSet<QUOTATION_ISSUES> QUOTATION_ISSUES { get; set; }
        public DbSet<DEPOSIT_COLLECTIONS> DEPOSIT_COLLECTIONS { get; set; }
        public DbSet<PROCESS_ORDERS> PROCESS_ORDERS { get; set; }
        public DbSet<PAYMENT_COLLECTIONS> PAYMENT_COLLECTIONS { get; set; }

        //Get all Status Contact
        public List<CONTACT_STATUS> GetAllContactStatus()
        {
            try
            {
                var Query = from p in CONTACT_STATUS
                            select p;
                List<CONTACT_STATUS> conS = Query.ToList();
                if (conS.Count > 0)
                    return conS;
                return null;
            }
            catch
            {
                return null;
            }
        }
        // get Status contact by name
        public CONTACT_STATUS GetStatusByName(string name)
        {
            try
            {
                var query = from cs in CONTACT_STATUS
                            where cs.STATUS_NAME.ToUpper() == name.ToUpper()
                            select cs;
                List<CONTACT_STATUS> css = query.ToList();
                if (css.Count > 0)
                    return css[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Add new Status Contact
        public int AddNewStatus(CONTACT_STATUS cs)
        {
            try
            {
                this.CONTACT_STATUS.Add(cs);
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }

        //Get Contact Detail by ID
        public CONTACT_DETAIL GetContactDetailByID(long id)
        {
            try
            {
                var Query = from conD in CONTACT_DETAIL
                            where conD.CONTACT_ID == id
                            select conD;
                List<CONTACT_DETAIL> conDs = Query.AsNoTracking().ToList();
                if (conDs != null)
                    return conDs[0];
                return null;
            }
            catch
            {
                return null;
            }
        }

        //Update Contact
        public int UpdateContact(CONTACT con, APPOINTMENT appoint, QUOTATION_ISSUES qiss, DEPOSIT_COLLECTIONS dc, PROCESS_ORDERS po, PAYMENT_COLLECTIONS paid)
        {
            var trans = this.Database.BeginTransaction();
            try
            {
                var original = CONTACTS.Find(con.ID);
                if (original != null)
                {
                    if (original.LOYALTY_STARTER_ID != con.LOYALTY_STARTER_ID)
                    {
                        InvoiceDAO invDAO = new InvoiceDAO();
                        INVOICE invoice = new INVOICE();
                        invoice.ASSIGNED_TO = con.ASSIGNED_USER_ID;
                        con = invDAO.getContactLoyaltyPoint(con, invoice, new ITEM());
                    }
                    Entry(original).CurrentValues.SetValues(con);
                    SaveChanges();
                    //Update or add new appointment   
                    if (appoint != null)
                    {
                        appoint.CONTACT_ID = con.ID;
                        var orgApp = APPOINTMENTS.Find(con.ID);
                        if (orgApp != null)
                        {
                            Entry(orgApp).CurrentValues.SetValues(appoint);
                            SaveChanges();
                        }
                        else
                        {
                            APPOINTMENTS.Add(appoint);
                            SaveChanges();
                        }
                    }
                    else
                    {
                        APPOINTMENTS.RemoveRange(APPOINTMENTS.Where(x => x.CONTACT_ID == con.ID));
                        SaveChanges();
                    }
                    //Update or add new quotation issues
                    if (qiss != null)
                    {
                        qiss.APPOINTMENT_ID = con.ID;
                        var orgQiss = QUOTATION_ISSUES.Find(con.ID);
                        if (orgQiss != null)
                        {
                            Entry(orgQiss).CurrentValues.SetValues(qiss);
                            SaveChanges();
                        }
                        else
                        {
                            QUOTATION_ISSUES.Add(qiss);
                            SaveChanges();
                        }
                    }
                    else
                    {
                        QUOTATION_ISSUES.RemoveRange(QUOTATION_ISSUES.Where(x => x.APPOINTMENT_ID == con.ID));
                        SaveChanges();
                    }
                    //Update or add new deposit collections
                    if (dc != null)
                    {
                        dc.QUOTATION_ISSUE_ID = con.ID;
                        var orgDC = DEPOSIT_COLLECTIONS.Find(con.ID);
                        if (orgDC != null)
                        {
                            Entry(orgDC).CurrentValues.SetValues(dc);
                            SaveChanges();
                        }
                        else
                        {
                            DEPOSIT_COLLECTIONS.Add(dc);
                            SaveChanges();
                        }
                    }
                    else
                    {
                        DEPOSIT_COLLECTIONS.RemoveRange(DEPOSIT_COLLECTIONS.Where(x => x.QUOTATION_ISSUE_ID == con.ID));
                        SaveChanges();
                    }
                    //Update or add new process orders
                    if (po != null)
                    {
                        po.DEPOSIT_COLLECTED_ID = con.ID;
                        var orgPO = PROCESS_ORDERS.Find(con.ID);
                        if (orgPO != null)
                        {
                            Entry(orgPO).CurrentValues.SetValues(po);
                            SaveChanges();
                        }
                        else
                        {
                            PROCESS_ORDERS.Add(po);
                            SaveChanges();
                        }
                    }
                    else
                    {
                        PROCESS_ORDERS.RemoveRange(PROCESS_ORDERS.Where(x => x.DEPOSIT_COLLECTED_ID == con.ID));
                        SaveChanges();
                    }
                    //Update or add new payment collection
                    if (paid != null)
                    {
                        paid.PROCESS_ORDER_ID = con.ID;
                        var orgPaid = PAYMENT_COLLECTIONS.Find(con.ID);
                        if (orgPaid != null)
                        {
                            Entry(orgPaid).CurrentValues.SetValues(paid);
                            SaveChanges();
                        }
                        else
                        {
                            PAYMENT_COLLECTIONS.Add(paid);
                            SaveChanges();
                        }
                    }
                    else
                    {
                        PAYMENT_COLLECTIONS.RemoveRange(PAYMENT_COLLECTIONS.Where(x => x.PROCESS_ORDER_ID == con.ID));
                        SaveChanges();
                    }
                }
                trans.Commit();
                return 1;
            }
            catch
            {
                trans.Rollback();
                return -1;
            }
        }
        //Get Contact By ID
        public CONTACT GetContactByID(long id)
        {
            try
            {
                var Query = from c in CONTACTS
                            where c.ID == id
                            select c;
                List<CONTACT> cons = Query.AsNoTracking().ToList();
                if (cons != null)
                    return cons[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get All Contact
        public List<CONTACT> ListAll()
        {
            try
            {
                var Query = from c in CONTACTS
                            select c;
                List<CONTACT> contacts = Query.AsNoTracking().ToList();
                if (contacts.Count > 0)
                    return contacts;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Delete Contact
        public int DeleteContact(long contactID)
        {
            try
            {
                CONTACTS.RemoveRange(CONTACTS.Where(x => x.ID == contactID));
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //Add new contact
        public int AddNewContact(CONTACT contact, APPOINTMENT appoint, QUOTATION_ISSUES qiss, DEPOSIT_COLLECTIONS dc, PROCESS_ORDERS po, PAYMENT_COLLECTIONS paid)
        {
            var trans = this.Database.BeginTransaction();
            try
            {
                this.CONTACTS.Add(contact);
                this.SaveChanges();
                //Add new appointment   
                if (appoint != null)
                {
                    appoint.CONTACT_ID = contact.ID;
                    APPOINTMENTS.Add(appoint);
                    SaveChanges();
                }
                //Update or add new quotation issues
                if (qiss != null)
                {
                    qiss.APPOINTMENT_ID = contact.ID;

                    QUOTATION_ISSUES.Add(qiss);
                    SaveChanges();
                }
                //Add new deposit collections
                if (dc != null)
                {
                    dc.QUOTATION_ISSUE_ID = contact.ID;
                    DEPOSIT_COLLECTIONS.Add(dc);
                    SaveChanges();
                }

                //Update or add new process orders
                if (po != null)
                {
                    po.DEPOSIT_COLLECTED_ID = contact.ID;
                    PROCESS_ORDERS.Add(po);
                    SaveChanges();
                }

                //Update or add new payment collection
                if (paid != null)
                {
                    paid.PROCESS_ORDER_ID = contact.ID;
                    PAYMENT_COLLECTIONS.Add(paid);
                    SaveChanges();
                }
                trans.Commit();
                return 1;
            }
            catch
            {

                trans.Rollback();
                return -1;
            }
        }
        //Add List Contacts from excel file
        public int AddListContactFromExcelFile(List<CONTACT> contacts)
        {
             var trans = this.Database.BeginTransaction();
             try
             {
                 if (contacts.Count != 0)
                 {
                     foreach (CONTACT con in contacts)
                     {
                         this.CONTACTS.Add(con);
                         this.SaveChanges();
                     }
                     trans.Commit();
                     return 1;
                 }
                 else
                     return -1;
             }
             catch
             {

                 trans.Rollback();
                 return -1;
             }
        }
        //Get sales pipeline
        public List<CONTACT_DETAIL> GetSalesPipeline(long? assignid, string finalStep, bool? value)
        {
            try
            {
                var orgQuery = from contact in CONTACT_DETAIL
                               where
                                    contact.ASSIGN_TO_ID == (assignid == null ? contact.ASSIGN_TO_ID : assignid)
                                    && finalStep != null ? ((contact.STATUS_FINAL_STEP == finalStep) ?
                                    (
                                        (contact.APPOINTMENT == (finalStep == "1" ? (value != null ? value : contact.APPOINTMENT) : contact.APPOINTMENT))
                                        && (contact.QUOTATION_ISSUE == (finalStep == "2" ? (value != null ? value : contact.QUOTATION_ISSUE) : contact.QUOTATION_ISSUE))
                                        && (contact.DEPOSIT_COLLECTED == (finalStep == "31" ? (value != null ? value : contact.DEPOSIT_COLLECTED) : contact.DEPOSIT_COLLECTED))
                                        && (contact.DEPOSIT_INVOICE_ISSUE == (finalStep == "32" ? (value != null ? value : contact.DEPOSIT_INVOICE_ISSUE) : contact.DEPOSIT_INVOICE_ISSUE))
                                        && (contact.PROCESS_ORDER_FULL_INVOICE_ISSUE == (finalStep == "4" ? (value != null ? value : contact.PROCESS_ORDER_FULL_INVOICE_ISSUE) : contact.PROCESS_ORDER_FULL_INVOICE_ISSUE))
                                        && (contact.PAYMENT_COLLECTION == (finalStep == "5" ? (value != null ? value : contact.PAYMENT_COLLECTION) : contact.PAYMENT_COLLECTION))
                                    ) : false
                                    ) : true
                                    && (contact.IS_CLOSE_CASE == null || (contact.IS_CLOSE_CASE != null && !(bool)contact.IS_CLOSE_CASE))
                               select contact;
                List<CONTACT_DETAIL> contacts = orgQuery.AsNoTracking().ToList();
                if (contacts.Count > 0)
                    return contacts;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Contact by condition
        public List<CONTACT_DETAIL> GetContactByCondition(DateTime? dateFrom, DateTime? dateTo,  string surname, string name, List<long?> ListOrgID, string primaryemail, string officephone, long? assignid)
        {
            try
            {
                List<CONTACT_DETAIL> sumcontacts = new List<CONTACT_DETAIL>();
                if (ListOrgID.Count > 0)
                {
                    foreach (long? OrgID in ListOrgID)
                    {
                        var orgQuery = from contact in CONTACT_DETAIL
                                       where
                                            contact.ASSIGN_TO_ID == (assignid == null ? contact.ASSIGN_TO_ID : assignid)
                                           && contact.OrgID == (OrgID == null ? contact.OrgID : OrgID)
                                           && (surname != "" ? (contact.SUR_NAME.ToUpper().Contains(surname.ToUpper())) : (true))
                                           && (name != "" ? (contact.NAME.ToUpper().Contains(name.ToUpper())) : (true))
                                           && (primaryemail != "" ? (contact.PRIMARY_EMAIL.ToUpper().Contains(primaryemail.ToUpper())) : (true))
                                           && (officephone != "" ? (contact.PHONE_OFFICE.ToUpper().Contains(officephone.ToUpper())) : (true))
                                 //          && contact.SUR_NAME.ToUpper() == (surname == "" ? contact.SUR_NAME.ToUpper() : surname.ToUpper())
                                 //          && contact.NAME.ToUpper() == (name == "" ? contact.NAME.ToUpper() : name.ToUpper())
                                  //         && contact.PRIMARY_EMAIL.ToUpper() == (primaryemail == "" ? contact.PRIMARY_EMAIL.ToUpper() : primaryemail.ToUpper())
                                  //         && contact.PHONE_OFFICE == (officephone == "" ? contact.PHONE_OFFICE : officephone)
                                           && (contact.DATE_ENTERED >= dateFrom && contact.DATE_ENTERED < dateTo)
                                       select contact;
                        List<CONTACT_DETAIL> contacts = orgQuery.AsNoTracking().ToList();
                        sumcontacts.AddRange(contacts);
                    }
                }
                else
                {
                    var orgQuery = from contact in CONTACT_DETAIL
                                   where
                                        contact.ASSIGN_TO_ID == (assignid == null ? contact.ASSIGN_TO_ID : assignid)
                                           && (surname != "" ? (contact.SUR_NAME.ToUpper().Contains(surname.ToUpper())) : (true))
                                           && (name != "" ? (contact.NAME.ToUpper().Contains(name.ToUpper())) : (true))
                                           && (primaryemail != "" ? (contact.PRIMARY_EMAIL.ToUpper().Contains(primaryemail.ToUpper())) : (true))
                                           && (officephone != "" ? (contact.PHONE_OFFICE.ToUpper().Contains(officephone.ToUpper())) : (true))
                                  //     && contact.SUR_NAME.ToUpper() == (surname == "" ? contact.SUR_NAME.ToUpper() : surname.ToUpper())
                                 //      && contact.NAME.ToUpper() == (name == "" ? contact.NAME.ToUpper() : name.ToUpper())
                                   //    && contact.PRIMARY_EMAIL.ToUpper() == (primaryemail == "" ? contact.PRIMARY_EMAIL.ToUpper() : primaryemail.ToUpper())
                                   //    && contact.PHONE_OFFICE == (officephone == "" ? contact.PHONE_OFFICE : officephone)
                                       && (contact.DATE_ENTERED >= dateFrom && contact.DATE_ENTERED < dateTo)
                                   select contact;
                    List<CONTACT_DETAIL> contacts = orgQuery.AsNoTracking().ToList();
                    sumcontacts.AddRange(contacts);
                }
                if (sumcontacts.Count > 0)
                    return sumcontacts;
                return null;
            }
            catch
            {
                return null;
            }
        }

        //Get Contact by condition
        public List<CONTACT_DETAIL> GetContactByConditionForPopUpWindow(DateTime? dateFrom, DateTime? dateTo, string surname, string name, List<long?> ListOrgID, string primaryemail, string officephone, long? assignid)
        {
            try
            {
                List<CONTACT_DETAIL> sumcontacts = new List<CONTACT_DETAIL>();
                foreach (long? OrgID in ListOrgID)
                {
                    var orgQuery = from contact in CONTACT_DETAIL
                                   where
                                        contact.ASSIGN_TO_ID == (assignid == null ? contact.ASSIGN_TO_ID : assignid)
                                       && contact.OrgID == (OrgID == null ? contact.OrgID : OrgID)
                                       && (surname != "" ? (contact.SUR_NAME.ToUpper().Contains(surname.ToUpper())) : (true))
                                       && (name != "" ? ( contact.NAME.ToUpper().Contains(name.ToUpper())) : (true))
                                       && (primaryemail != "" ? (contact.PRIMARY_EMAIL.ToUpper().Contains(primaryemail.ToUpper())) : (true))
                                       && (officephone != "" ? (contact.PHONE_OFFICE.ToUpper().Contains(officephone.ToUpper())) : (true))
                                       && (contact.DATE_ENTERED >= dateFrom && contact.DATE_ENTERED < dateTo)
                                   select contact;
                    List<CONTACT_DETAIL> contacts = orgQuery.AsNoTracking().ToList();
                    sumcontacts.AddRange(contacts);
                }
                if (sumcontacts.Count > 0)
                    return sumcontacts;
                return null;
            }
            catch
            {
                return null;
            }
        }
    }
}
